{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "blind-apollo",
   "metadata": {},
   "source": [
    "# Convert raw data to JSON for package"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "knowing-publication",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>Year</th>\n",
       "      <th>Fossil fuels electricity generation</th>\n",
       "      <th>Geothermal electricity generation</th>\n",
       "      <th>Hydroelectricity generation</th>\n",
       "      <th>Nuclear power generation</th>\n",
       "      <th>Solar electricity generation</th>\n",
       "      <th>Wind electricity generation</th>\n",
       "      <th>country_id</th>\n",
       "      <th>country_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1980</td>\n",
       "      <td>0.23100</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.711</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>107</td>\n",
       "      <td>Afghanistan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1981</td>\n",
       "      <td>0.27100</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.721</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>107</td>\n",
       "      <td>Afghanistan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>1982</td>\n",
       "      <td>0.24500</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.707</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>107</td>\n",
       "      <td>Afghanistan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>1983</td>\n",
       "      <td>0.25400</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.746</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>107</td>\n",
       "      <td>Afghanistan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>1984</td>\n",
       "      <td>0.26200</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.757</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>107</td>\n",
       "      <td>Afghanistan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7632</th>\n",
       "      <td>7632</td>\n",
       "      <td>2015</td>\n",
       "      <td>4.31178</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.940</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.008</td>\n",
       "      <td>0.0</td>\n",
       "      <td>106</td>\n",
       "      <td>Zimbabwe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7633</th>\n",
       "      <td>7633</td>\n",
       "      <td>2016</td>\n",
       "      <td>3.71488</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.955</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.009</td>\n",
       "      <td>0.0</td>\n",
       "      <td>106</td>\n",
       "      <td>Zimbabwe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7634</th>\n",
       "      <td>7634</td>\n",
       "      <td>2017</td>\n",
       "      <td>3.21856</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.929</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.011</td>\n",
       "      <td>0.0</td>\n",
       "      <td>106</td>\n",
       "      <td>Zimbabwe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7635</th>\n",
       "      <td>7635</td>\n",
       "      <td>2018</td>\n",
       "      <td>3.93202</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.014</td>\n",
       "      <td>0.0</td>\n",
       "      <td>106</td>\n",
       "      <td>Zimbabwe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7636</th>\n",
       "      <td>7636</td>\n",
       "      <td>2019</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.014</td>\n",
       "      <td>0.0</td>\n",
       "      <td>106</td>\n",
       "      <td>Zimbabwe</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7637 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Unnamed: 0  Year  Fossil fuels electricity generation  \\\n",
       "0              0  1980                              0.23100   \n",
       "1              1  1981                              0.27100   \n",
       "2              2  1982                              0.24500   \n",
       "3              3  1983                              0.25400   \n",
       "4              4  1984                              0.26200   \n",
       "...          ...   ...                                  ...   \n",
       "7632        7632  2015                              4.31178   \n",
       "7633        7633  2016                              3.71488   \n",
       "7634        7634  2017                              3.21856   \n",
       "7635        7635  2018                              3.93202   \n",
       "7636        7636  2019                                  NaN   \n",
       "\n",
       "      Geothermal electricity generation  Hydroelectricity generation  \\\n",
       "0                                   0.0                        0.711   \n",
       "1                                   0.0                        0.721   \n",
       "2                                   0.0                        0.707   \n",
       "3                                   0.0                        0.746   \n",
       "4                                   0.0                        0.757   \n",
       "...                                 ...                          ...   \n",
       "7632                                0.0                        4.940   \n",
       "7633                                0.0                        2.955   \n",
       "7634                                0.0                        3.929   \n",
       "7635                                0.0                        5.000   \n",
       "7636                                0.0                          NaN   \n",
       "\n",
       "      Nuclear power generation  Solar electricity generation  \\\n",
       "0                          0.0                         0.000   \n",
       "1                          0.0                         0.000   \n",
       "2                          0.0                         0.000   \n",
       "3                          0.0                         0.000   \n",
       "4                          0.0                         0.000   \n",
       "...                        ...                           ...   \n",
       "7632                       0.0                         0.008   \n",
       "7633                       0.0                         0.009   \n",
       "7634                       0.0                         0.011   \n",
       "7635                       0.0                         0.014   \n",
       "7636                       0.0                         0.014   \n",
       "\n",
       "      Wind electricity generation  country_id country_name  \n",
       "0                             0.0         107  Afghanistan  \n",
       "1                             0.0         107  Afghanistan  \n",
       "2                             0.0         107  Afghanistan  \n",
       "3                             0.0         107  Afghanistan  \n",
       "4                             0.0         107  Afghanistan  \n",
       "...                           ...         ...          ...  \n",
       "7632                          0.0         106     Zimbabwe  \n",
       "7633                          0.0         106     Zimbabwe  \n",
       "7634                          0.0         106     Zimbabwe  \n",
       "7635                          0.0         106     Zimbabwe  \n",
       "7636                          0.0         106     Zimbabwe  \n",
       "\n",
       "[7637 rows x 10 columns]"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df_nrg=pd.read_csv(\"world_energy_mix.csv\")\n",
    "df_nrg"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "curious-naples",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['FIFA', 'Dial', 'ISO3166-1-Alpha-3', 'MARC', 'is_independent',\n",
       "       'ISO3166-1-numeric', 'GAUL', 'FIPS', 'WMO', 'ISO3166-1-Alpha-2', 'ITU',\n",
       "       'IOC', 'DS', 'UNTERM Spanish Formal', 'Global Code',\n",
       "       'Intermediate Region Code', 'official_name_fr', 'UNTERM French Short',\n",
       "       'ISO4217-currency_name', 'Developed / Developing Countries',\n",
       "       'UNTERM Russian Formal', 'UNTERM English Short',\n",
       "       'ISO4217-currency_alphabetic_code',\n",
       "       'Small Island Developing States (SIDS)', 'UNTERM Spanish Short',\n",
       "       'ISO4217-currency_numeric_code', 'UNTERM Chinese Formal',\n",
       "       'UNTERM French Formal', 'UNTERM Russian Short', 'M49',\n",
       "       'Sub-region Code', 'Region Code', 'official_name_ar',\n",
       "       'ISO4217-currency_minor_unit', 'UNTERM Arabic Formal',\n",
       "       'UNTERM Chinese Short', 'Land Locked Developing Countries (LLDC)',\n",
       "       'Intermediate Region Name', 'official_name_es', 'UNTERM English Formal',\n",
       "       'official_name_cn', 'official_name_en', 'ISO4217-currency_country_name',\n",
       "       'Least Developed Countries (LDC)', 'Region Name', 'UNTERM Arabic Short',\n",
       "       'Sub-region Name', 'official_name_ru', 'Global Name', 'Capital',\n",
       "       'Continent', 'TLD', 'Languages', 'Geoname ID', 'CLDR display name',\n",
       "       'EDGAR'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_p = pd.read_csv(\"https://raw.githubusercontent.com/datasets/country-codes/master/data/country-codes.csv\")\n",
    "\n",
    "df_p.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "numeric-membership",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_country = df_p[['ISO3166-1-Alpha-3', 'CLDR display name', 'official_name_en', 'Region Name']]\n",
    "df_country.columns = ['iso_code', 'label', 'official_name_en', 'region']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "affected-television",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df_nrg[[\"country_name\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "digital-tablet",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "190"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.drop_duplicates(keep='first', inplace=False, ignore_index=True)\n",
    "len(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "controversial-compensation",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "250"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df_country)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "clinical-custom",
   "metadata": {},
   "outputs": [],
   "source": [
    "def fix_name(df):\n",
    "    df.replace(\"Antigua and Barbuda\", \"Antigua & Barbuda\",inplace=True)\n",
    "    df.replace(\"Bosnia and Herzegovina\", \"Bosnia\",inplace=True)\n",
    "    df.replace(\"Burma (Myanmar)\", \"Myanmar\",inplace=True)\n",
    "    df.replace(\"Democratic Republic of the Congo\", \"Congo - Kinshasa\",inplace=True)\n",
    "    df.replace(\"Ivory Coast\", \"Côte d’Ivoire\",inplace=True)\n",
    "    df.replace(\"Macao\", \"Macau\",inplace=True)\n",
    "    df.replace(\"Republic of the Congo\", \"Congo - Brazzaville\",inplace=True)\n",
    "    df.replace(\"Saint Lucia\", \"St. Lucia\",inplace=True)\n",
    "    df.replace(\"Saint Vincent and the Grenadines\", \"St. Vincent & Grenadines\",inplace=True)\n",
    "    df.replace(\"Sao Tome and Principe\", \"São Tomé & Príncipe\",inplace=True)\n",
    "    df.replace(\"Swaziland\", \"Eswatini\",inplace=True)\n",
    "    df.replace(\"Trinidad and Tobago\", \"Trinidad & Tobago\",inplace=True)\n",
    "    df.replace(\"United Kingdom\", \"UK\",inplace=True)\n",
    "    df.replace(\"USA\", \"US\",inplace=True)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "objective-graduate",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = fix_name(df)\n",
    "df_nrg = fix_name(df_nrg)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "polar-diana",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_join = df.merge(df_country, how=\"left\", left_on='country_name', right_on='label', indicator=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "taken-score",
   "metadata": {},
   "outputs": [],
   "source": [
    "assert len(df_join.query(\"_merge == 'left_only'\")) == 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "strategic-margin",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>country_name</th>\n",
       "      <th>iso_code</th>\n",
       "      <th>label</th>\n",
       "      <th>official_name_en</th>\n",
       "      <th>region</th>\n",
       "      <th>_merge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>AFG</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Asia</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Albania</td>\n",
       "      <td>ALB</td>\n",
       "      <td>Albania</td>\n",
       "      <td>Albania</td>\n",
       "      <td>Europe</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Algeria</td>\n",
       "      <td>DZA</td>\n",
       "      <td>Algeria</td>\n",
       "      <td>Algeria</td>\n",
       "      <td>Africa</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Angola</td>\n",
       "      <td>AGO</td>\n",
       "      <td>Angola</td>\n",
       "      <td>Angola</td>\n",
       "      <td>Africa</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Antigua &amp; Barbuda</td>\n",
       "      <td>ATG</td>\n",
       "      <td>Antigua &amp; Barbuda</td>\n",
       "      <td>Antigua and Barbuda</td>\n",
       "      <td>Americas</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>185</th>\n",
       "      <td>Venezuela</td>\n",
       "      <td>VEN</td>\n",
       "      <td>Venezuela</td>\n",
       "      <td>Venezuela (Bolivarian Republic of)</td>\n",
       "      <td>Americas</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>186</th>\n",
       "      <td>Vietnam</td>\n",
       "      <td>VNM</td>\n",
       "      <td>Vietnam</td>\n",
       "      <td>Viet Nam</td>\n",
       "      <td>Asia</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>187</th>\n",
       "      <td>Yemen</td>\n",
       "      <td>YEM</td>\n",
       "      <td>Yemen</td>\n",
       "      <td>Yemen</td>\n",
       "      <td>Asia</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>188</th>\n",
       "      <td>Zambia</td>\n",
       "      <td>ZMB</td>\n",
       "      <td>Zambia</td>\n",
       "      <td>Zambia</td>\n",
       "      <td>Africa</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>189</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>ZWE</td>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>Africa</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>190 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          country_name iso_code              label  \\\n",
       "0          Afghanistan      AFG        Afghanistan   \n",
       "1              Albania      ALB            Albania   \n",
       "2              Algeria      DZA            Algeria   \n",
       "3               Angola      AGO             Angola   \n",
       "4    Antigua & Barbuda      ATG  Antigua & Barbuda   \n",
       "..                 ...      ...                ...   \n",
       "185          Venezuela      VEN          Venezuela   \n",
       "186            Vietnam      VNM            Vietnam   \n",
       "187              Yemen      YEM              Yemen   \n",
       "188             Zambia      ZMB             Zambia   \n",
       "189           Zimbabwe      ZWE           Zimbabwe   \n",
       "\n",
       "                       official_name_en    region _merge  \n",
       "0                           Afghanistan      Asia   both  \n",
       "1                               Albania    Europe   both  \n",
       "2                               Algeria    Africa   both  \n",
       "3                                Angola    Africa   both  \n",
       "4                   Antigua and Barbuda  Americas   both  \n",
       "..                                  ...       ...    ...  \n",
       "185  Venezuela (Bolivarian Republic of)  Americas   both  \n",
       "186                            Viet Nam      Asia   both  \n",
       "187                               Yemen      Asia   both  \n",
       "188                              Zambia    Africa   both  \n",
       "189                            Zimbabwe    Africa   both  \n",
       "\n",
       "[190 rows x 6 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_join"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "processed-elimination",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_final = df_nrg.merge(df_join[['country_name', 'iso_code', 'official_name_en', 'region']], how=\"left\", left_on='country_name', right_on='country_name', indicator=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "chinese-explosion",
   "metadata": {},
   "outputs": [],
   "source": [
    "assert len(df_final.query(\"_merge == 'left_only'\")) == 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "australian-medicaid",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_final.drop(columns=['Unnamed: 0', '_merge'], inplace=True)\n",
    "df_final.columns\n",
    "df_final.columns=['year', 'fossil_TWh', 'geothermal_TWh', 'hydroelectricity_TWh',\n",
    "       'nuclear_TWh', 'solar_TWh', 'wind_TWh', 'country_id', 'country_name', 'iso_code',\n",
    "       'official_name_en', 'region']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "found-realtor",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th>fossil_TWh</th>\n",
       "      <th>geothermal_TWh</th>\n",
       "      <th>hydroelectricity_TWh</th>\n",
       "      <th>nuclear_TWh</th>\n",
       "      <th>solar_TWh</th>\n",
       "      <th>wind_TWh</th>\n",
       "      <th>country_id</th>\n",
       "      <th>country_name</th>\n",
       "      <th>iso_code</th>\n",
       "      <th>official_name_en</th>\n",
       "      <th>region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1523</th>\n",
       "      <td>2018</td>\n",
       "      <td>4707.118620</td>\n",
       "      <td>0.123500</td>\n",
       "      <td>1187.20801</td>\n",
       "      <td>272.17599</td>\n",
       "      <td>177.200000</td>\n",
       "      <td>365.800000</td>\n",
       "      <td>115</td>\n",
       "      <td>China</td>\n",
       "      <td>CHN</td>\n",
       "      <td>China</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1524</th>\n",
       "      <td>2019</td>\n",
       "      <td>4812.964500</td>\n",
       "      <td>0.123500</td>\n",
       "      <td>1254.46460</td>\n",
       "      <td>330.12201</td>\n",
       "      <td>224.100000</td>\n",
       "      <td>405.700000</td>\n",
       "      <td>115</td>\n",
       "      <td>China</td>\n",
       "      <td>CHN</td>\n",
       "      <td>China</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7356</th>\n",
       "      <td>2020</td>\n",
       "      <td>2419.230282</td>\n",
       "      <td>16.930105</td>\n",
       "      <td>291.11084</td>\n",
       "      <td>789.91864</td>\n",
       "      <td>132.630753</td>\n",
       "      <td>337.509815</td>\n",
       "      <td>48</td>\n",
       "      <td>US</td>\n",
       "      <td>USA</td>\n",
       "      <td>United States of America</td>\n",
       "      <td>Americas</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      year   fossil_TWh  geothermal_TWh  hydroelectricity_TWh  nuclear_TWh  \\\n",
       "1523  2018  4707.118620        0.123500            1187.20801    272.17599   \n",
       "1524  2019  4812.964500        0.123500            1254.46460    330.12201   \n",
       "7356  2020  2419.230282       16.930105             291.11084    789.91864   \n",
       "\n",
       "       solar_TWh    wind_TWh  country_id country_name iso_code  \\\n",
       "1523  177.200000  365.800000         115        China      CHN   \n",
       "1524  224.100000  405.700000         115        China      CHN   \n",
       "7356  132.630753  337.509815          48           US      USA   \n",
       "\n",
       "              official_name_en    region  \n",
       "1523                     China      Asia  \n",
       "1524                     China      Asia  \n",
       "7356  United States of America  Americas  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_final.query(\"wind_TWh > 337\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "sized-dakota",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Taiwan has no region and no official_name_en in the dataset, let's fix this\n",
    "df_final.loc[df_final.iso_code == 'TWN', ['official_name_en', 'region']] = 'Taiwan', 'Asia'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "spatial-blackjack",
   "metadata": {},
   "outputs": [],
   "source": [
    "# df_final.query(\"iso_code == 'TWN'\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "after-premises",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'year': 2020,\n",
       " 'fossil_TWh': 44.553,\n",
       " 'geothermal_TWh': 0.155753680282,\n",
       " 'hydroelectricity_TWh': 60.75027,\n",
       " 'nuclear_TWh': 335.41461,\n",
       " 'solar_TWh': 14.1455621272,\n",
       " 'wind_TWh': 43.5316709709,\n",
       " 'country_id': 194,\n",
       " 'country_name': 'France',\n",
       " 'iso_code': 'FRA',\n",
       " 'official_name_en': 'France',\n",
       " 'region': 'Europe',\n",
       " 'total_TWh': 498.55086677838193}"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "energy_mix = {}\n",
    "for code in df_final.iso_code.unique():\n",
    "#for code in ['FRA', 'AFG', 'VNM']:\n",
    "    df=df_final.query(\"iso_code == @code\")\n",
    "    max_year = df.year.max()\n",
    "    for year in range(max_year, 1980, -1):\n",
    "        # print(year)\n",
    "        df_tmp=df_final.query(\"iso_code == @code and year == @year\")\n",
    "        #print(df_tmp.isna().values.any())\n",
    "        #print(df_tmp)\n",
    "        if df_tmp.isnull().values.any() == False:\n",
    "            energy_mix[code]=df_tmp.iloc[0].to_dict()\n",
    "            total=0\n",
    "            for k, v in energy_mix[code].items():\n",
    "                if \"_TWh\" in k:\n",
    "                    total += v\n",
    "            energy_mix[code][\"total_TWh\"]=total\n",
    "#             energy_mix[code][\"total\"]=energy_mix[code][\"fossil\"]+energy_mix[code][\"geothermal\"]+\\\n",
    "#                 energy_mix[code][\"hydroelectricity\"]+energy_mix[code][\"nuclear\"]+\\\n",
    "#                 energy_mix[code][\"solar\"]+energy_mix[code][\"wind\"]\n",
    "            break\n",
    "    else:\n",
    "        print(\"ERROR: missing values for all year for \", code)\n",
    "        print(df)\n",
    "    #print(df_tmp)\n",
    "energy_mix['FRA']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "tropical-scholar",
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "with open(\"global_energy_mix.json\", \"w\") as outfile:\n",
    "    json.dump(energy_mix, outfile)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "future-opinion",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "leximpact",
   "language": "python",
   "name": "leximpact"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
